﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using PMQLCHH.DAO;


namespace PMQLCHH.DAO
{
    class HoaDonDAO
    {
        //Kha Nguyen - Hoa Don DAO - 6/4/2013 - begin 
        /// <summary>
        /// Them Hoa Don
        /// </summary>
        /// 
        public bool ThemHoaDon(HoaDonDTO HD)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("INSERT INTO HOADON(MaHoaDon,MaKhachHang,TenNguoiNhan,SoDienThoaiNguoiNhan,DiaChiGiao,NgayGiao,Vat,PhiVanChuyen,TongTien,TraTruoc,CongNo,GhiChu,TrangThai) VALUES ('{0}','{1}',N'{2}','{3}',N'{4}','{5}',{6},{7},{8},{9},{10},N'{11}',{12})", HD.StrMaHoaDon, HD.StrMaKhachHang, HD.StrTenNguoiNhan, HD.StrSoDienThoaiNguoiNhan, HD.StrDiaChiGiao, HD.DtNgayGiao, HD.IVat, HD.FPhiVanChuyen, HD.FTongTien, HD.FTraTruoc, HD.FConNo, HD.StrGhiChu, HD.ITrangThai);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Xoa Hoa Don
        /// </summary>
        /// 
        public bool XoaHoaDon(string MaHD)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("DELETE FROM HOADON WHERE MaHoaDon = '{0}'", MaHD);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Cap Nhat Hoa Don
        /// </summary>
        /// 
        public bool CapNhatHoaDon(HoaDonDTO HD)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("UPDATE HOADON SET MaKhachHang = '{0}', TenNguoiNhan = N'{1}', SoDienThoaiNguoiNhan = '{2}', DiaChiGiao = N'{3}', NgayGiao = '{4}', Vat = {5}, PhiVanChuyen = {6}, TongTien = {7}, TraTruoc = {8}, CongNo = {9}, GhiChu = N'{10}', TrangThai = {11} WHERE MaHoaDon = '{12}' ", HD.StrMaKhachHang, HD.StrTenNguoiNhan, HD.StrSoDienThoaiNguoiNhan, HD.StrDiaChiGiao, HD.DtNgayGiao, HD.IVat, HD.FPhiVanChuyen, HD.FTongTien, HD.FTraTruoc, HD.FConNo, HD.StrGhiChu, HD.ITrangThai, HD.StrMaHoaDon);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }



        /// <summary>
        /// Them Chi Tiet Hoa Don
        /// </summary>
        /// 
        public bool ThemChiTietHoaDon(ChiTietHoaDonDTO CT)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("INSERT INTO CHITIETHOADON(MaHoaDon,MaMatHang,SoLuong,GiaBan,ThanhTien,TrangThai) VALUES ('{0}','{1}',{2},{3},{4},'{5}')",  CT.StrMaHoaDon, CT.StrMaMatHang, CT.ISoLuong, CT.FGiaBan, CT.FThanhTien, CT.ITrangThai);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }


        /// <summary>
        /// Xoa Chi Tiet Hoa Don
        /// </summary>
        /// 
        public bool XoaChiTietHoaDon(string MaCT)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("DELETE FROM CHITIETHOADON WHERE MaChiTietHoaDon = '{0}'", MaCT);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Xoa Chi Tiet Hoa Don bang ma hoa don
        /// </summary>
        /// 
        public bool XoaChiTietHoaDonTuHoaDon(string MaHD)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("DELETE FROM CHITIETHOADON WHERE MaHoaDon = '{0}'", MaHD);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Cap Nhat Chi Tiet Hoa Don
        /// </summary>
        /// 
        public bool CapNhatChiTietHoaDon(ChiTietHoaDonDTO CT)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("UPDATE CHITIETHOADON SET = MaHoaDon = '{0}', MaMatHang = '{1}', SoLuong = '{2}', GiaBan = '{3}', ThanhTien = {4}, TrangThai = {5}, NgayLap = {6} WHERE MaChiTietHoaDon = '{7}')", CT.StrMaHoaDon, CT.StrMaMatHang, CT.ISoLuong, CT.FGiaBan, CT.FThanhTien, CT.ITrangThai, CT.DtNgayLap, CT.IMaChiTietHoaDon);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Lay ma hoa don moi nhat
        /// </summary>
        /// 
        public DataTable LayMaHoaDonMoiNhat()
        {
            string strQuery = "";
            strQuery = "SELECT TOP 1 MaHoaDon FROM HOADON ORDER BY MaHoaDon DESC";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay ma Chi Tiet Hoa Don moi nhat
        /// </summary>
        /// 
        public DataTable LayMaChiTietHoaDonMoiNhat()
        {
            string strQuery = "";
            strQuery = "SELECT TOP 1 MaChiTietHoaDon FROM CHITIETHOADON ORDER BY MaChiTietHoaDon DESC";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay ngay lap hoa don cu nhat
        /// </summary>
        /// 
        public DataTable LayMaNgayLapHoaDonCuNhat()
        {
            string strQuery = "";
            strQuery = "SELECT TOP 1 NgayLap FROM HOADON ORDER BY NgayLap ASC";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay danh sach hoa don
        /// </summary>
        /// 
        public DataTable LayDanhSachHoaDon()
        {
            string strQuery = "";
            strQuery = "SELECT MaHoaDon,KH.TenKhachHang,KH.SoDienThoai,NgayGiao,TongTien,CASE HD.TrangThai WHEN 0 THEN N'Chưa Thanh Toán' ELSE N'Đã Thanh Toán' END AS 'TrangThai',HD.NgayLap FROM HOADON HD,KHACHHANG KH WHERE HD.MaKhachHang = KH.MaKhachHang";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }



        /// <summary>
        /// lay chi tiet hoa don theo hoa don
        /// </summary>
        /// 
        public DataTable LayChiTietHoaDon(string MaHD)
        {
            string strQuery = "";
            strQuery = String.Format("SELECT * FROM HOADON WHERE MaHoaDon = '{0}'", MaHD);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay danh sach san pham trong chi tiet hoa don theo hoa don
        /// </summary>
        /// 
        public DataTable LayDSMHChiTietHoaDon(string MaHD)
        {
            string strQuery = "";
            strQuery = String.Format("SELECT MH.MaMatHang,MH.TenMatHang,L.TenLoaiMatHang,D.TenDonVi,SoLuong,GiaBan,ThanhTien FROM CHITIETHOADON CT, MATHANG MH, DONVITINH D, LOAIMATHANG L WHERE CT.MaMatHang = MH.MaMatHang AND MH.LoaiMatHang = L.MaLoaiMatHang AND MH.DonViTinh = D.MaDonVi AND CT.MaHoaDon = '{0}'", MaHD);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }


        /// <summary>
        /// loc danh sach  hoa don theo cac tieu chi Ma Hoa Don va trang thai, tu ngay den ngay
        /// </summary>
        /// 
        public DataTable LocHoaDon(string MaHD, string strTuNgay, string strDenNgay, int TrangThai)
        {
            string strQuery = "";
            strQuery = String.Format("SELECT MaHoaDon,KH.TenKhachHang,KH.SoDienThoai,NgayGiao,TongTien,CASE HD.TrangThai WHEN 0 THEN N'Chưa Thanh Toán' ELSE N'Đã Thanh Toán' END AS 'TrangThai',HD.NgayLap FROM HOADON HD,KHACHHANG KH WHERE HD.MaKhachHang = KH.MaKhachHang AND MaHoaDon LIKE  '%{0}%' AND HD.NgayLap BETWEEN '{1}' AND '{2} 23:59:59' ", MaHD, strTuNgay, strDenNgay);
            if (TrangThai != 2)//trang thai la chon tat ca
            {
                strQuery += String.Format("AND HD.TrangThai = {0}", TrangThai);
            }
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }


        //datetimepicker1.value.ToString("MM/dd/yyyy") 
        //date=DateTime.ParseExact(dateTimePicker1.value.ToS tring(),"dd/MM/yyyy",null); 
        //Kha Nguyen - Hoa Don DAO - 6/4/2013 - end


        //Quang Dat Them Cho Phan Bao Cao Thong Ke
        #region Quang Dat Them Bao Cao Thong Ke 09-06-2013

        public static DataTable LayDanhSachHoaDonTheoNgay(string tungay, string denngay)
        {
            string sql = "";
            sql += " SELECT hd.MaHoaDon,hd.MaKhachHang,kh.TenKhachHang,hd.TongTien,hd.TrangThai";
            sql += " FROM HoaDon hd, KhachHang kh";
            sql += " where hd.MaKhachHang=kh.MaKhachHang";
            sql += " and convert(varchar,hd.NgayLap,103) Between '" + tungay.ToString() + "' and '" + denngay + "'";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);

            DataTable dt_new = new DataTable();

            if (dt.Rows.Count > 0)
            {
                dt_new.Columns.Add("MaHoaDon", typeof(string));
                dt_new.Columns.Add("MaKhachHang", typeof(string));
                dt_new.Columns.Add("TenKhachHang", typeof(string));
                dt_new.Columns.Add("TongTien", typeof(float));
                dt_new.Columns.Add("TrangThai", typeof(int));
                dt_new.Columns.Add("SoLuong", typeof(int));

                
                //Lay so luong san pham trong chi tiet hoa don theo ma hoa don

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    double sl = 0;
                    sql="";
                    sql="Select SoLuong from ChiTietHoaDon where MaHoaDon='"+dt.Rows[i]["MaHoaDon"].ToString()+"'";
                    DataTable dt_sl = KetNoiCSDLDAO.ExecuteQuery(sql);
                    if (dt_sl.Rows.Count > 0)
                    {
                        for (int j = 0; j < dt_sl.Rows.Count; j++)
                        {
                            sl += double.Parse(dt_sl.Rows[j]["SoLuong"].ToString());
                        }
                    }
                    DataRow row = dt_new.NewRow();
                    row["MaHoaDon"] = dt.Rows[i]["MaHoaDon"].ToString();
                    row["MaKhachHang"] = dt.Rows[i]["MaKhachHang"].ToString();
                    row["TenKhachHang"] = dt.Rows[i]["TenKhachHang"].ToString();
                    row["TongTien"] = dt.Rows[i]["TongTien"].ToString();
                    row["TrangThai"] = dt.Rows[i]["TrangThai"].ToString();
                    row["SoLuong"] = sl.ToString();
                    dt_new.Rows.Add(row);
                }

            }

            return dt_new;
        }


        public static DataTable Load_List_HoaDon_Theo_Ngay(string ngay)
        {
            string sql = "";
            sql += " select hd.MaHoaDon,kh.TenKhachHang,convert(nvarchar,hd.NgayLap,103) as NgayLap,hd.TenNguoiNhan,hd.DiaChiGiao,hd.TraTruoc,hd.CongNo,hd.TrangThai";
            sql += " from HoaDon hd,KhachHang kh";
            sql += " where hd.MaKhachHang=kh.MaKhachHang";
            sql += " and convert(nvarchar,hd.NgayGiao,103)='"+ngay.ToString()+"'";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;

        }

        #endregion

    }
}
